package com.example.mapper;

import com.example.entity.User;
import com.example.entity.vo.MonthData;
import com.example.entity.vo.WeekData;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface AdminMapper {

    List<User> pageList(Integer page, Integer pageSize, String username, String email);

    @Delete("delete from user where id=#{id}")
    void deleteUser(Integer id);

    @Select("select COUNT(*) from user")
    Integer getTotal();

    @Select("SELECT COUNT(*) AS user_count\n" +
            "FROM user\n" +
            "WHERE register_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')\n" +
            "  AND register_time < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01');\n")
    Integer getadded();

    @Select("SELECT COUNT(DISTINCT id) AS user_count  -- 假设你有一个唯一标识用户的字段，比如 user_id\n" +
            "FROM user\n" +
            "WHERE DATE(login_time) = CURDATE();")
    Integer getActive();

    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, login_time, logout_time)) AS average_online_minutes\n" +
            "FROM user\n" +
            "WHERE DATE(login_time) = CURDATE()\n" +
            "  AND login_time <= logout_time;")
    Integer getAverage();

    @Select("SELECT\n" +
            "    CASE\n" +
            "        WHEN non_today_users = 0 THEN 100.0\n" +
            "        ELSE (today_users / non_today_users) * 100\n" +
            "    END AS growth_rate\n" +
            "FROM\n" +
            "    (SELECT\n" +
            "        SUM(CASE WHEN DATE(register_time) = CURDATE() THEN 1 ELSE 0 END) AS today_users,\n" +
            "        SUM(CASE WHEN DATE(register_time) != CURDATE() THEN 1 ELSE 0 END) AS non_today_users\n" +
            "     FROM user) AS user_counts;")
    Float getTotalRate();


    @Select("SELECT\n" +
            "    CASE\n" +
            "        WHEN last_month_users = 0 THEN 100.0 -- 如果上个月新增用户为0，则增长率为100%\n" +
            "        ELSE ((current_month_users - last_month_users) / last_month_users) * 100 -- 否则计算增长率\n" +
            "    END AS growth_rate\n" +
            "FROM\n" +
            "    (SELECT\n" +
            "        COUNT(CASE WHEN DATE_FORMAT(register_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m') THEN 1 END) AS current_month_users, -- 本月新增用户数量\n" +
            "        COUNT(CASE WHEN DATE_FORMAT(register_time, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') THEN 1 END) AS last_month_users -- 上个月新增用户数量\n" +
            "     FROM user) AS monthly_user_counts;")
    Float getAddedRate();

    @Select("SELECT\n" +
            "    CASE\n" +
            "        WHEN yesterday_users = 0 THEN NULL -- 或者0、100等默认值\n" +
            "        ELSE (today_users / yesterday_users) * 100\n" +
            "    END AS activity_growth_rate\n" +
            "FROM\n" +
            "    (SELECT\n" +
            "        COUNT(CASE WHEN DATE(login_time) = CURDATE() THEN 1 END) AS today_users,\n" +
            "        COUNT(CASE WHEN DATE(login_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 1 END) AS yesterday_users\n" +
            "     FROM user) AS daily_user_counts;")
    Float getActiveRate();

    @Select("SELECT\n" +
            "    CASE\n" +
            "        WHEN yesterday_avg_online = 0 THEN NULL -- 如果昨日平均在线时长为0，则增长率为未定义，返回NULL\n" +
            "        ELSE (today_avg_online / yesterday_avg_online) * 100 -- 否则计算增长率并转换为百分比\n" +
            "    END AS avg_online_growth_rate\n" +
            "FROM\n" +
            "    (SELECT\n" +
            "        AVG(TIMESTAMPDIFF(MINUTE, login_time, logout_time)) AS today_avg_online -- 今日平均在线时长\n" +
            "     FROM user\n" +
            "     WHERE DATE(login_time) = CURDATE()\n" +
            "AND login_time <= logout_time" +
            "    ) AS today_stats,\n" +
            "    (SELECT\n" +
            "        AVG(TIMESTAMPDIFF(MINUTE, login_time, logout_time)) AS yesterday_avg_online -- 昨日平均在线时长\n" +
            "     FROM user\n" +
            "     WHERE DATE(login_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)\n" +
            "AND login_time <= logout_time" +
            "    ) AS yesterday_stats;")
    Float getAverageRate();

    @Select("select month from active")
    List<String> getMonthsData();

    @Select("select total from active")
    List<Integer> getTotals();

    @Select("WITH RECURSIVE DateSeries AS (\n" +
            "    SELECT CURDATE() - INTERVAL 6 DAY AS login_date -- 从今天开始往前数6天\n" +
            "    UNION ALL\n" +
            "    SELECT login_date + INTERVAL 1 DAY\n" +
            "    FROM DateSeries\n" +
            "    WHERE login_date < CURDATE() -- 确保日期不超过今天\n" +
            ")\n" +
            "SELECT\n" +
            "    ds.login_date, -- 返回具体的年月日日期\n" +
            "    COUNT(u.id) AS login_count -- 假设user表有一个唯一标识符id，计算每天的登录人数\n" +
            "FROM\n" +
            "    DateSeries ds\n" +
            "LEFT JOIN\n" +
            "    user u ON DATE(u.login_time) = ds.login_date -- 左连接user表，按日期匹配\n" +
            "GROUP BY\n" +
            "    ds.login_date\n" +
            "ORDER BY\n" +
            "    ds.login_date DESC; -- 按日期降序排序，这样今天会在最前面")
    List<WeekData> getWeekData();
}
